<!DOCTYPE html>
<html>
<head>
    <title>08.多表连接查询</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="selected">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 08.多表连接查询 </h1>
            <div class="doc_post"> <p class="md_block">

<div class="toc"><ul>
<li>
<a href="#toc_0">08.多表连接查询</a>
<ul>
<li>
<a href="#toc_1">内连接[INNER] JOIN</a>
</li>
<li>
<a href="#toc_2">外连接</a>
<ul>
<li>
<a href="#toc_3">左连接 LEFT JOIN</a>
</li>
<li>
<a href="#toc_4">右连接 RIGHT JOIN</a>
</li>
<li>
<a href="#toc_5">全连接 FULL JOIN</a>
</li>
</ul>
</li>
<li>
<a href="#toc_6">[理解]笛卡尔积：CROSS JOIN</a>
</li>
<li>
<a href="#toc_7">多表进行连接</a>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">内连接[INNER] JOIN</h2>

<p class="md_block">
    <span class="md_line">内连接（inner join）:产生的结果是A和B的交集（相同列里面的相同值,A表要有,B表也要有才显示）<br /></span>
    <span class="md_line">内连接查询能将左表和右表中能关联起来的数据连接后返回，返回的结果就是两个表中所有相匹配的数据。<br /></span>
    <span class="md_line md_line_dom_embed"><img onerror="this.src='../_image/2018-03-26-10-25-46.jpg';this.onerror=null;"  class="" src="/_image/2018-03-26-10-25-46.jpg" alt="" title="" ></span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="err">表</span><span class="mi">1</span> <span class="p">[</span><span class="k">INNER</span><span class="p">]</span> <span class="k">JOIN</span> <span class="err">表</span><span class="mi">2</span> <span class="k">ON</span> <span class="err">表</span><span class="mi">1</span><span class="p">.</span><span class="err">列名</span> <span class="o">=</span> <span class="err">表</span><span class="mi">2</span><span class="p">.</span><span class="err">列名</span><span class="p">;</span>
<span class="c1">-- 下面语句不规范的写法,来源于早期版本,注意下.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="err">表</span><span class="mi">1</span><span class="p">,</span><span class="err">表</span><span class="mi">2</span> <span class="k">WHERE</span> <span class="err">表</span><span class="mi">1</span><span class="p">.</span><span class="err">列名</span> <span class="o">=</span> <span class="err">表</span><span class="mi">2</span><span class="p">.</span><span class="err">列名</span><span class="p">;</span>

<span class="c1">-- =======================================================</span>
<span class="c1">-- 内连接 [INNER] JOIN</span>
    <span class="c1">--SELECT stu_info.uname,dorm_info.dorm_name FROM stu_info INNER JOIN dorm_info ON stu_info.dorm_id = dorm_info.dorm_id</span>
    <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">dorm_info</span> <span class="k">ON</span> <span class="n">stu_info</span><span class="p">.</span><span class="n">dorm_id</span> <span class="o">=</span> <span class="n">dorm_info</span><span class="p">.</span><span class="n">dorm_id</span>
    <span class="c1">-- 老版本的写法如下:</span>
    <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">,</span><span class="n">dorm_info</span> <span class="k">WHERE</span> <span class="n">stu_info</span><span class="p">.</span><span class="n">dorm_id</span> <span class="o">=</span> <span class="n">dorm_info</span><span class="p">.</span><span class="n">dorm_id</span>
    
    <span class="c1">-- 注意:要给表起别名才方便</span>
    <span class="c1">-- 内连接,左表与右表都有的数据(关联列)才显示.</span>
    <span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="n">dorm_name</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">AS</span> <span class="n">a</span> <span class="k">JOIN</span> <span class="n">dorm_info</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">dorm_id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">dorm_id</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">b</span><span class="p">.</span><span class="n">dorm_name</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_2">外连接</h2>

<p class="md_block md_has_block_below md_has_block_below_blockquote">
    <span class="md_line">内连接是要显示两张表的内存，而外连接不要求如此，外连接可以依据连接表保留左表，右表或全部表的行为而分为左外连接右外连接和全连接。</span>
</p>


<blockquote>

<p class="md_block">
    <span class="md_line">注意,在MYSQL中,外连接分为左连接(LEFT JOIN)与右连接(RIGHT JOIN),没有全连接(FULL [OUTER] JOIN).</span>
</p>

</blockquote>

<h3 id="toc_3">左连接 LEFT JOIN</h3>

<p class="md_block">
    <span class="md_line">Left Join:产生表B的完全集，而A表中匹配的则有值（没有匹配的则以null值取代）<br /></span>
    <span class="md_line md_line_dom_embed"><img onerror="this.src='../_image/2018-03-26-10-24-53.jpg';this.onerror=null;"  class="" src="/_image/2018-03-26-10-24-53.jpg" alt="" title="" ></span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="err">表</span><span class="mi">1</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="err">表</span><span class="mi">2</span> <span class="k">ON</span> <span class="err">表</span><span class="mi">1</span><span class="p">.</span><span class="err">列名</span> <span class="o">=</span> <span class="err">表</span><span class="mi">2</span><span class="p">.</span><span class="err">列名</span><span class="p">;</span>
    <span class="c1">--左连接,左表所有行(关联列所有行)显示,右表有的则显示,没有的则显示NULL</span>
    <span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">dorm_id</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="n">a</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">dorm_info</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">dorm_id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">dorm_id</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_4">右连接 RIGHT JOIN</h3>

<p class="md_block md_has_block_below md_has_block_below_img">
    <span class="md_line">Right Join:产生表A的完全集，而B表中匹配的则有值（没有匹配的则以null值取代）</span>
</p>

<img onerror="this.src='../_image/2018-03-26-10-25-32.jpg';this.onerror=null;"  class="" src="/_image/2018-03-26-10-25-32.jpg" alt="" title="" ><div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="err">表</span><span class="mi">1</span> <span class="k">RIGHT</span> <span class="k">JOIN</span> <span class="err">表</span><span class="mi">2</span> <span class="k">ON</span> <span class="err">表</span><span class="mi">1</span><span class="p">.</span><span class="err">列名</span> <span class="o">=</span> <span class="err">表</span><span class="mi">2</span><span class="p">.</span><span class="err">列名</span><span class="p">;</span>
    <span class="c1">--右连接,右表所有行(关联列所有行)显示,左表有的则显示,没有的则显示NULL</span>
    <span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">dorm_id</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="n">a</span> <span class="k">RIGHT</span> <span class="k">JOIN</span> <span class="n">dorm_info</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">dorm_id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">dorm_id</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_5">全连接 FULL JOIN</h3>

<p class="md_block">
    <span class="md_line">FULL Join:产生表A的完全集，B也是完全值,哪边没有的就用NULL填充.<br /></span>
    <span class="md_line md_line_dom_embed"><img onerror="this.src='../_image/2019-11-15-11-18-36.jpg';this.onerror=null;"  class="" src="/_image/2019-11-15-11-18-36.jpg" alt="" title="" ></span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="err">表</span><span class="mi">1</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="err">表</span><span class="mi">2</span> <span class="k">ON</span> <span class="err">表</span><span class="mi">1</span><span class="p">.</span><span class="err">列名</span> <span class="o">=</span> <span class="err">表</span><span class="mi">2</span><span class="p">.</span><span class="err">列名</span><span class="p">;</span>
    <span class="c1">--全连接,左右表都显示,有关联则关联,没关联则显示NULL</span>
    <span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">dorm_id</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="n">a</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="n">dorm_info</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">dorm_id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">dorm_id</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_6">[理解]笛卡尔积：CROSS JOIN</h2>

<p class="md_block">
    <span class="md_line">不用记语法,只要用JOIN时,不加ON A.列名 = B.列名.它查出来的结果就是笛卡尔积,它更像是一个BUG.</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 笛卡尔积:就像是语法错误.它产生的数据量是(左边*右边)的记录数.</span>
    <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span><span class="c1">-- 125条数据</span>
    <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">dorm_info</span><span class="p">;</span> <span class="c1">-- 72条数据</span>
    <span class="k">SELECT</span> <span class="n">a</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">dorm_id</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="n">a</span><span class="p">,</span><span class="n">dorm_info</span> <span class="n">b</span> <span class="c1">-- 9000条记录</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_7">多表进行连接</h2>

<p class="md_block">
    <span class="md_line">多表进行连接查询时,尽量加上别名,要注意字段的选择.用 <code>表别名.列名</code>,条件中也用<code>表别名.列名</code>.</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 查询用户表,显示孙涛的所有科目成绩</span>
<span class="k">SELECT</span>
<span class="n">a</span><span class="p">.</span><span class="n">id</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="k">c</span><span class="p">.</span><span class="n">sname</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="k">sum</span>  <span class="c1">-- 注意别用关键字做为列名,如sum</span>
<span class="k">FROM</span> 
<span class="n">kp_user_info</span> <span class="k">AS</span> <span class="n">a</span> 
<span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">kp_score</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">on</span> <span class="n">a</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">uid</span>
<span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">kp_subject</span> <span class="k">AS</span> <span class="k">c</span> <span class="k">on</span> <span class="n">b</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id</span>  
<span class="k">WHERE</span> <span class="n">a</span><span class="p">.</span><span class="n">uname</span> <span class="o">=</span><span class="s1">&#39;孙涛&#39;</span><span class="p">;</span>

<span class="c1">-- 注意多种写法,表顺序的问题</span>
<span class="c1">-- 注意:从语义上看,好像会显示kp_score的所有行,但实际上与上面语句一样的.只显示孙涛用户的成绩.(WHERE是把三表连接起来后(一个整体虚拟表一样)再进行条件筛选).</span>
<span class="k">SELECT</span>
<span class="n">b</span><span class="p">.</span><span class="n">id</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="k">c</span><span class="p">.</span><span class="n">sname</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="k">sum</span> <span class="c1">-- -- 注意别用关键字做为列名,如sum</span>
<span class="k">FROM</span>
<span class="n">kp_score</span> <span class="k">AS</span> <span class="n">a</span>
<span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">kp_user_info</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">uid</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> 
<span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">kp_subject</span> <span class="k">AS</span> <span class="k">c</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id</span>
<span class="k">WHERE</span> <span class="n">b</span><span class="p">.</span><span class="n">uname</span> <span class="o">=</span> <span class="s1">&#39;孙涛&#39;</span><span class="p">;</span>

<span class="c1">-- 上面语句可以写成如下样子.把条件放在kp_user_info表中,用右连接</span>
<span class="k">SELECT</span>
<span class="n">b</span><span class="p">.</span><span class="n">id</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="k">c</span><span class="p">.</span><span class="n">sname</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="k">sum</span>
<span class="k">FROM</span>
<span class="n">kp_score</span> <span class="k">AS</span> <span class="n">a</span>
<span class="k">RIGHT</span> <span class="k">JOIN</span> <span class="p">(</span><span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">WHERE</span> <span class="n">uname</span> <span class="o">=</span> <span class="s1">&#39;孙涛&#39;</span><span class="p">)</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">uid</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> 
<span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">kp_subject</span> <span class="k">AS</span> <span class="k">c</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id</span><span class="p">;</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>